--- $Id: dbinit.txt 1729 2006-11-12 23:03:16Z benalex $

--- Sample Hypersonic SQL compatible schema and data
---
--- All Acegi Security JDBC DAOs can be customised to use a different schema.
--- In addition, the Acegi Security JDBC DAOs do not even need to be used
--- with Acegi Security, and an entirely customised persistence strategy
--- can be employed via standard interfaces (eg in-memory, Hibernate etc).

SET IGNORECASE TRUE;

CREATE TABLE users (
	username VARCHAR(50) NOT NULL PRIMARY KEY,
	password VARCHAR(50) NOT NULL,
	enabled BIT NOT NULL
);

CREATE TABLE authorities (
	username VARCHAR(50) NOT NULL,
	authority VARCHAR(50) NOT NULL
);
CREATE UNIQUE INDEX ix_auth_username ON authorities (username, authority);

ALTER TABLE authorities ADD CONSTRAINT fk_authorities_users foreign key (username) REFERENCES users(username);

INSERT INTO users VALUES ('david', 'newyork', true);
INSERT INTO users VALUES ('alex', 'newjersey', true);
INSERT INTO users VALUES ('tim', 'illinois', true);

INSERT INTO authorities VALUES ('david', 'ROLE_USER');
INSERT INTO authorities VALUES ('david', 'ROLE_ADMIN');
INSERT INTO authorities VALUES ('alex', 'ROLE_USER');
INSERT INTO authorities VALUES ('tim', 'ROLE_USER');

--- Indexes auto created in HSQLDB for primary keys and unique columns

--CREATE TABLE acl_object_identity (
--     id BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
--     object_identity VARCHAR_IGNORECASE(250) NOT NULL,
--     parent_object BIGINT,
--     acl_class VARCHAR_IGNORECASE(250) NOT NULL,
--    CONSTRAINT unique_object_identity UNIQUE(object_identity),
--     FOREIGN KEY (parent_object) REFERENCES acl_object_identity(id)
--);

--CREATE TABLE acl_permission (
--     id BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0)  NOT NULL PRIMARY KEY,
--     acl_object_identity BIGINT NOT NULL,
--     recipient VARCHAR_IGNORECASE(100) NOT NULL,
--     mask INTEGER NOT NULL,
--     CONSTRAINT unique_recipient UNIQUE(acl_object_identity, recipient),
--     FOREIGN KEY (acl_object_identity) REFERENCES acl_object_identity(id)
--);

--- Mask integer 0  = no permissions
--- Mask integer 1  = administer
--- Mask integer 2  = read
--- Mask integer 6  = read and write permissions
--- Mask integer 14 = read and write and create permissions

---------------------------------------------------------------------
--- *** INHERITED RIGHTS FOR DIFFERENT INSTANCES AND RECIPIENTS ***
--- INSTANCE  RECIPIENT         PERMISSION(S) (COMMENT #INSTANCE)
---------------------------------------------------------------------
---    1      ROLE_SUPERVISOR   Administer
---    2      ROLE_SUPERVISOR   None (overrides parent #1)
---           marissa           Read
---    3      ROLE_SUPERVISOR   Administer (from parent #1)
---           scott             Read, Write, Create
---    4      ROLE_SUPERVISOR   Administer (from parent #1)
---    5      ROLE_SUPERVISOR   Administer (from parent #3)
---           scott             Read, Write, Create (from parent #3)
---    6      ROLE_SUPERVISOR   Administer (from parent #3)
---           scott             Administer (overrides parent #3)
---------------------------------------------------------------------

--INSERT INTO acl_object_identity VALUES (1, 'org.acegisecurity.acl.DomainObject:1', null, 'org.acegisecurity.acl.basic.SimpleAclEntry');
--INSERT INTO acl_object_identity VALUES (2, 'org.acegisecurity.acl.DomainObject:2', 1, 'org.acegisecurity.acl.basic.SimpleAclEntry');
--INSERT INTO acl_object_identity VALUES (3, 'org.acegisecurity.acl.DomainObject:3', 1, 'org.acegisecurity.acl.basic.SimpleAclEntry');
--INSERT INTO acl_object_identity VALUES (4, 'org.acegisecurity.acl.DomainObject:4', 1, 'org.acegisecurity.acl.basic.SimpleAclEntry');
--INSERT INTO acl_object_identity VALUES (5, 'org.acegisecurity.acl.DomainObject:5', 3, 'org.acegisecurity.acl.basic.SimpleAclEntry');
--INSERT INTO acl_object_identity VALUES (6, 'org.acegisecurity.acl.DomainObject:6', 3, 'org.acegisecurity.acl.basic.SimpleAclEntry');

--INSERT INTO acl_permission VALUES (null, 1, 'ROLE_SUPERVISOR', 1);
--INSERT INTO acl_permission VALUES (null, 2, 'ROLE_SUPERVISOR', 0);
--INSERT INTO acl_permission VALUES (null, 2, 'marissa', 2);
--INSERT INTO acl_permission VALUES (null, 3, 'scott', 14);
--INSERT INTO acl_permission VALUES (null, 6, 'scott', 1);
